package com.yuki.fwwb.DAO;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
@Mapper
public interface UsersDAO {
    /**
     * 年龄段分析
     */
//    @Select("SELECT nld AS 'age_structure', count( * ) AS 'num' FROM(SELECT CASE WHEN (DATE_FORMAT(NOW(),'%Y')-birth_year)<7" +
//            " THEN '童年'  WHEN (DATE_FORMAT(NOW(),'%Y')-birth_year)>=7 AND  (DATE_FORMAT(NOW(),'%Y')-birth_year)<18 THEN " +
//            "'少年' WHEN (DATE_FORMAT(NOW(),'%Y')-birth_year)>=18 AND  (DATE_FORMAT(NOW(),'%Y')-birth_year)<40" +
//            " THEN '青年' WHEN (DATE_FORMAT(NOW(),'%Y')-birth_year)>=40 and  (DATE_FORMAT(NOW(),'%Y')-birth_year)<66 THEN" +
//            " '中年' WHEN (DATE_FORMAT(NOW(),'%Y')-birth_year)>=66 THEN '老年' END AS nld FROM users ) a GROUP BY nld ")
//    List<Map> findAgeStructure();
    @Select("SELECT\n" +
            "\tnld AS '年龄段',\n" +
            "\tcount( * ) AS '人数' \n" +
            "FROM\n" +
            "\t(\n" +
            "\tSELECT\n" +
            "\tCASE\t\n" +
            "\t\tWHEN\n" +
            "\t\t\t (DATE_FORMAT(NOW(),'%Y')-birth_year)<7  ## 1999-04-08 16:06:23\n" +
            "\t\t\tTHEN    ## 2019-04-08 15:53:52\n" +
            "\t\t\t\t'童年 0-7' \n" +
            "\t\t\t\t\n" +
            "\t\t\t\tWHEN (DATE_FORMAT(NOW(),'%Y')-birth_year)>=7 AND  (DATE_FORMAT(NOW(),'%Y')-birth_year)<18 THEN   ## 1999-04-08 15:31:27\n" +
            "\t\t\t\t\t'少年 7-17' \n" +
            "\t\t\t\t\t\n" +
            "\t\t\t\tWHEN (DATE_FORMAT(NOW(),'%Y')-birth_year)>=18 AND  (DATE_FORMAT(NOW(),'%Y')-birth_year)<29 THEN ## 1989-04-08 15:31:08\n" +
            "\t\t\t\t\t'青年 18-28'\n" +
            "\t\t\t\t\t\n" +
            "\t\t\t\tWHEN (DATE_FORMAT(NOW(),'%Y')-birth_year)>=29 and  (DATE_FORMAT(NOW(),'%Y')-birth_year)<41 THEN ## 1979-04-08 15:54:15\n" +
            "\t\t\t\t\t'中年 29-40'\n" +
            "\t\t\t\tWHEN (DATE_FORMAT(NOW(),'%Y')-birth_year)>=41 and  (DATE_FORMAT(NOW(),'%Y')-birth_year)<60 THEN ## 1979-04-08 15:54:15\n" +
            "\t\t\t\t\t'中老年 41-59'\n" +
            "\t\t\t  WHEN (DATE_FORMAT(NOW(),'%Y')-birth_year)>=60 THEN ## 1969-04-08 16:04:02\n" +
            "\t\t\t\t\t'老年 >60'\n" +
            "\t\t\t\t\t\tEND AS nld \n" +
            "\t\t\t\tFROM\n" +
            "\t\t\t\tusers\n" +
            "\t\t\t) a \n" +
            "GROUP BY\n" +
            "\t\t\t\tnld")
    List<Map> findAgeStructure();
}
